package com.baohua.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.Region;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.baohua.exception.BaohuaSystemException;

/**
 * Excel导入导出功能
 * 
 * @author czp
 * 
 * @param <T>
 */
public class ExcelUtil<T> {
	Class<T> clazz;

	public ExcelUtil(Class<T> clazz) {
		this.clazz = clazz;
	}

	/*
	 * 导入Excel
	 */
	public List<T> importExcel(File file) throws IllegalArgumentException,
			IllegalAccessException, IOException, InstantiationException,
			BaohuaSystemException {
		List<T> list = new ArrayList<T>();
		BufferedInputStream bis = new BufferedInputStream(new FileInputStream(
				file));
		Workbook book;
		if (file.getName().endsWith("xls")) {
			book = new HSSFWorkbook(bis);
		} else {
			book = new XSSFWorkbook(bis);
		}
		Sheet sheet = book.getSheetAt(0);// 如果传入的sheet名不存在则默认指向第1个sheet.
		int rows = sheet.getLastRowNum();// 得到数据的行数
		Row templeRow = sheet.getRow(rows);
		String templeText = templeRow.getCell(0).getStringCellValue();
		if (StringUtils.equals(templeText, clazz.getName())) {
			if (rows > 2) {// 有数据时才处理
				Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
				Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
				for (Field field : allFields) {
					// 将有注解的field存放到map中.
					if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
						ExcelVOAttribute attr = field
								.getAnnotation(ExcelVOAttribute.class);
						int col = attr.order() - 1;// 获得列号
						field.setAccessible(true);// 设置类的私有字段属性可访问.
						fieldsMap.put(col, field);
					}
				}
				for (int i = 2; i < rows; i++) {// 从第3行开始取数据,默认第一行是表头.
					Row row = sheet.getRow(i);// 得到一行中的所有单元格对象.
					T entity = null;
					for (int j = 1; j < row.getLastCellNum(); j++) {
						Cell c = row.getCell(j);

						entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
						// System.out.println(cells[j].getContents());
						Field field = fieldsMap.get(j);// 从map中得到对应列的field.
						// 取得类型,并根据对象类型设置值.

						switch (c.getCellType()) {
						case Cell.CELL_TYPE_STRING:
							field.set(entity, c.getRichStringCellValue()
									.getString());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							if (DateUtil.isCellDateFormatted(c)) {
								field.set(entity, c.getDateCellValue());
							} else {
								field.set(entity, c.getNumericCellValue());
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:
							field.set(entity, c.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_FORMULA:
							field.set(entity, c.getCellFormula());
							break;
						}
					}
					if (entity != null) {
						list.add(entity);
					}
				}
			}

		} else {
			throw new BaohuaSystemException("只能通过模板导入, 请下载模板导入!");
		}
		return list;
	}

	/**
	 * 对list数据源将其里面的数据导入到excel表单
	 * 
	 * @param sheetName
	 *            工作表的名称
	 * @param sheetSize
	 *            每个sheet中数据的行数,此数值必须小于65536
	 * @param output
	 *            java输出流
	 * @return 
	 * @throws IOException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	public void exportExcel(List<T> list, String sheetName,
			boolean isTemplate, OutputStream output) throws IOException,
			IllegalArgumentException, IllegalAccessException {

		Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
		List<Field> fields = new ArrayList<Field>();
		// 得到所有field并存放到一个list中.
		for (Field field : allFields) {
			if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
				fields.add(field);
			}
		}

		HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象

		// excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑.
		double sheetNo = Math.ceil(list.size() / 65536);// 取出一共有多少个sheet.
		for (int index = 0; index <= sheetNo; index++) {
			HSSFSheet sheet = workbook.createSheet();// 产生工作表对象
			workbook.setSheetName(index, sheetName + index);// 设置工作表的名称.
			HSSFRow row;
			HSSFCell cell;// 产生单元格
			// 设置大标题行, 合并单元格
			sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (fields
					.size() - 1)));

			Row titleRow = sheet.createRow(0);
			Cell titleCell = titleRow.createCell(0);
			// 设置大标题行样式
			titleRow.setHeightInPoints((short) 30);
			CellStyle style = workbook.createCellStyle();
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setBorderRight(CellStyle.BORDER_THIN);
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setAlignment(CellStyle.ALIGN_CENTER);
			HSSFFont font = workbook.createFont();
			font.setFontHeightInPoints((short) 20);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style.setFont(font);

			titleCell.setCellStyle(style);
			// 设置大标题行的名称为工作薄名称
			titleCell.setCellValue(sheetName);
			// 创建一行作为标题行
			row = sheet.createRow(1);
			row.setHeightInPoints((short) 15);
			// 写入各个字段的列头名称
			for (int i = 0; i < fields.size(); i++) {
				Field field = fields.get(i);
				ExcelVOAttribute attr = field
						.getAnnotation(ExcelVOAttribute.class);
				int col = attr.order() - 1;// 获得列号
				cell = row.createCell(col);// 创建列
				// 设置标题行的单元格宽度
				sheet.setColumnWidth(i, 18 * 256);

				// 设置标题行的样式
				HSSFCellStyle titleStyle = workbook.createCellStyle();
				titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
				titleStyle.setBorderRight(CellStyle.BORDER_THIN);
				titleStyle.setBorderTop(CellStyle.BORDER_THIN);
				titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
				titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
				HSSFFont font1 = workbook.createFont();
				font1.setFontHeightInPoints((short) 12);
				font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				titleStyle.setFont(font1);
				cell.setCellStyle(titleStyle);

				cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
				if (attr.mustInput()) {
					HSSFRichTextString hSSFRichTextString = new HSSFRichTextString(
							attr.name() + "*");
					HSSFFont font2 = workbook.createFont();
					font2.setColor(HSSFColor.RED.index);
					hSSFRichTextString.applyFont(attr.name().length(), attr
							.name().length() + 1, font2);
					cell.setCellValue(hSSFRichTextString);// 写入列名
				} else {
					cell.setCellValue(attr.name());// 写入列名
				}

				Row templateRow = sheet.createRow(fields.size() + 1);
				templateRow.createCell(0).setCellValue(clazz.getName());
				templateRow.setZeroHeight(true);

				// 如果设置了isDate, 输入必须是日期格式
				if (attr.isDate()) {
					setHSSFDate(sheet, 2, 100, col, col);// 这里默认设了2-101列只能选择不能输入.
				}

				// 如果设置了提示信息则鼠标放上去提示.
				if (!attr.prompt().trim().equals("")) {
					setHSSFPrompt(sheet, "提示:", attr.prompt(), 2, 100, col, col);// 这里默认设了2-101列提示.
				}

				// 如果设置了combo属性则本列只能选择不能输入
				if (attr.combo().length > 0) {
					setHSSFValidation(sheet, attr.combo(), 2, 100, col, col);// 这里默认设了2-101列只能选择不能输入.
				}
			}
			if (!isTemplate) {
				// index为第几个工作薄的索引
				int startNo = index * 65536;
				int endNo = Math.min(startNo + 65536, list.size());
				// 写入各条记录,每条记录对应excel表中的一行
				for (int i = startNo; i < endNo; i++) {
					// 从每个工作薄的第三3行开始
					row = sheet.createRow(i + 2 - startNo);
					//

					T vo = (T) list.get(i); // 得到导出对象.
					for (int j = 0; j < fields.size(); j++) {
						Field field = fields.get(j);// 获得field.
						field.setAccessible(true);// 设置实体类私有属性可访问
						ExcelVOAttribute attr = field
								.getAnnotation(ExcelVOAttribute.class);
						// 设置序号
						cell = row.createCell(0);// 创建cell
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						// 设置data单元格样式
						HSSFCellStyle hcs = workbook.createCellStyle();
						hcs.setBorderLeft(CellStyle.BORDER_THIN);
						hcs.setBorderRight(CellStyle.BORDER_THIN);
						hcs.setBorderTop(CellStyle.BORDER_THIN);
						hcs.setBorderBottom(CellStyle.BORDER_THIN);
						cell.setCellStyle(hcs);

						cell.setCellValue(i + 1);

						// 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
						if (attr.isExport()) {
							cell = row.createCell(attr.order() - 1);// 创建cell
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							// 设置data单元格样式
							cell.setCellStyle(hcs);
							if(attr.convertBool().length!=0) {
								cell.setCellValue(((Boolean) field.get(vo)) ? attr.convertBool()[0]
										: attr.convertBool()[1]);
							}else {
								cell.setCellValue(field.get(vo) == null ? ""
										: String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
							}
						}
					}
				}
			}

		}
		output.flush();
		workbook.write(output);
		output.close();
	}

	/**
	 * 设置单元格上提示
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param promptTitle
	 *            标题
	 * @param promptContent
	 *            内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,
			String promptContent, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 构造constraint对象
		DVConstraint constraint = DVConstraint
				.createCustomFormulaConstraint("DD1");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,
				endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_view = new HSSFDataValidation(
				regions, constraint);
		data_validation_view.createPromptBox(promptTitle, promptContent);
		sheet.addValidationData(data_validation_view);
		return sheet;
	}

	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param textlist
	 *            下拉框显示的内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFDate(HSSFSheet sheet, int firstRow,
			int endRow, int firstCol, int endCol) {
		CellRangeAddressList addressList = new CellRangeAddressList(firstRow,
				endRow, firstCol, endCol);
		DVConstraint dvConstraint = DVConstraint.createDateConstraint(
				OperatorType.BETWEEN, "1900-01-01", "2999-12-12", "yyyy-MM-dd");

		DataValidation dataValidation = new HSSFDataValidation(addressList,
				dvConstraint);
		dataValidation.setSuppressDropDownArrow(true);

		dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
		dataValidation.createErrorBox("请输入一个数字", "yyyy-MM-dd");

		sheet.addValidationData(dataValidation);
		return sheet;
	}

	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param textlist
	 *            下拉框显示的内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
			String[] textlist, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint
				.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,
				endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(
				regions, constraint);
		sheet.addValidationData(data_validation_list);
		return sheet;
	}
}
